<!DOCTYPE html>
<html lang="en">





<head>
  <meta charset="UTF-8">
  <link rel="apple-touch-icon" sizes="76x76" href="/img/favicon.png">
  <link rel="icon" type="image/png" href="/img/favicon.png">
  <meta name="viewport"
        content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no, shrink-to-fit=no">
  <meta http-equiv="x-ua-compatible" content="ie=edge">
  
  <meta name="theme-color" content="#2f4154">
  <meta name="description" content="My Personal Website For Blog">
  <meta name="author" content="黄宇辉">
  <meta name="keywords" content="learning note,hexo blog">
  <title>Spring:数据库开发 - 欢迎参观小灰灰的网站哟 ヾ(◍°∇°◍)ﾉﾞ ~</title>

  <link  rel="stylesheet" href="https://cdn.staticfile.org/twitter-bootstrap/4.4.1/css/bootstrap.min.css" />


  <link  rel="stylesheet" href="https://cdn.staticfile.org/github-markdown-css/4.0.0/github-markdown.min.css" />
  <link  rel="stylesheet" href="/lib/hint/hint.min.css" />

  
    <link  rel="stylesheet" href="https://cdn.staticfile.org/highlight.js/10.0.0/styles/vs2015.min.css" />
  

  


<!-- 主题依赖的图标库，不要自行修改 -->
<link rel="stylesheet" href="//at.alicdn.com/t/font_1749284_yg9cfy8wd6.css">

<link rel="stylesheet" href="//at.alicdn.com/t/font_1736178_pjno9b9zyxs.css">

<link  rel="stylesheet" href="/css/main.css" />

<!-- 自定义样式保持在最底部 -->


  <script  src="/js/utils.js" ></script>
</head>


<body>
  <header style="height: 70vh;">
    <nav id="navbar" class="navbar fixed-top  navbar-expand-lg navbar-dark scrolling-navbar">
  <div class="container">
    <a class="navbar-brand"
       href="/">&nbsp;<strong>ANONYMOUS</strong>&nbsp;</a>

    <button id="navbar-toggler-btn" class="navbar-toggler" type="button" data-toggle="collapse"
            data-target="#navbarSupportedContent"
            aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
      <div class="animated-icon"><span></span><span></span><span></span></div>
    </button>

    <!-- Collapsible content -->
    <div class="collapse navbar-collapse" id="navbarSupportedContent">
      <ul class="navbar-nav ml-auto text-center">
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/">
                <i class="iconfont icon-home-fill"></i>
                Home
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/archives/">
                <i class="iconfont icon-archive-fill"></i>
                Archives
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/tags/">
                <i class="iconfont icon-tags-fill"></i>
                Tags
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/about/">
                <i class="iconfont icon-user-fill"></i>
                About
              </a>
            </li>
          
        
        
          <li class="nav-item" id="search-btn">
            <a class="nav-link" data-toggle="modal" data-target="#modalSearch">&nbsp;&nbsp;<i
                class="iconfont icon-search"></i>&nbsp;&nbsp;</a>
          </li>
        
      </ul>
    </div>
  </div>
</nav>

    <div class="view intro-2" id="background" parallax=true
         style="background: url('/img/default.png') no-repeat center center;
           background-size: cover;">
      <div class="full-bg-img">
        <div class="mask flex-center" style="background-color: rgba(0, 0, 0, 0.3)">
          <div class="container text-center white-text fadeInUp">
            <span class="h2" id="subtitle">
              
            </span>

            
              
  <div class="mt-3 post-meta">
    <i class="iconfont icon-date-fill" aria-hidden="true"></i>
    <time datetime="2019-05-16 15:00">
      May 16, 2019 pm
    </time>
  </div>


<div class="mt-1">
  
    
    <span class="post-meta mr-2">
      <i class="iconfont icon-chart"></i>
      2.1k 字
    </span>
  

  
    
    <span class="post-meta mr-2">
      <i class="iconfont icon-clock-fill"></i>
      
      
      32
       分钟
    </span>
  

  
  
</div>

            
          </div>

          
        </div>
      </div>
    </div>
  </header>

  <main>
    
      

<div class="container-fluid">
  <div class="row">
    <div class="d-none d-lg-block col-lg-2"></div>
    <div class="col-lg-8 nopadding-md">
      <div class="container nopadding-md" id="board-ctn">
        <div class="py-5" id="board">
          <div class="post-content mx-auto" id="post">
            
            <article class="markdown-body">
              <h2 id="学习笔记-Spring-数据库开发"><a href="#学习笔记-Spring-数据库开发" class="headerlink" title="学习笔记 : Spring 数据库开发"></a>学习笔记 : Spring 数据库开发</h2><h3 id="Spring-JDBC"><a href="#Spring-JDBC" class="headerlink" title="Spring JDBC"></a>Spring JDBC</h3><p><em>简介 : Spring框架降低了Java EE API的使用难度,例如JDBC. Spring的JDBC模块负责数据库资源管理和错误处理,简化了开发人员对数据库的操作,从而将更多的精力投入到编写业务逻辑中.</em></p>
<h4 id="Spring-JdbcTemplate-的解析"><a href="#Spring-JdbcTemplate-的解析" class="headerlink" title="Spring JdbcTemplate 的解析"></a>Spring JdbcTemplate 的解析</h4><p><em>针对数据库操作,Spring框架提供了<code>JdbcTemplate</code>类,该类是Spring框架数据抽象层的基础,Spring JDBC的核心类. 它继承自抽象类<code>JdbcAccessor</code>,同时实现了<code>JdbcOperations</code>接口.</em></p>
<ol>
<li>JdbcAccessor : 该类为子类提供了一些访问是数据库时使用的公共属性.</li>
<li>JdbcOperations : 该接口定义了在<code>JdbcTemplate</code>类中可以使用的操作集合,包括增删改查等操作.</li>
</ol>
<h4 id="Spring-JDBC-的配置"><a href="#Spring-JDBC-的配置" class="headerlink" title="Spring JDBC 的配置"></a>Spring JDBC 的配置</h4><p><em>Spring JDBC模块主要由4个包组成,如下所示哟 ~</em></p>
<ol>
<li><code>core</code>(核心包) : 包含了JDBC的核心功能,包括JdbcTemplate,SimpleJdbcInsert,SimpleJdbcCall类,以及NamedParameterJdbcTemplate类.</li>
<li><code>dataSource</code>(数据源包) : 访问数据源的实用工具类,它有多种数据源的实现,可以在Java EE容器外部测试JDBC代码.</li>
<li><code>object</code>(对象包) : 以面向对象的方式访问数据库,它允许执行查询并将返回结果作为业务对象,可以在数据表的列和业务对象的属性之间映射查询结果.</li>
<li><code>support</code>(支持包) : 包含了core和object包的支持类,例如: 提供异常转换功能的SQLException类.</li>
</ol>
<p><em>由此可知,Spring对数据库的操作都封装在这几个包中,Spring JDBC的配置是在配置文件<code>applicationContext.xml</code>中完成的哟 ~ 其模板如下所示 :</em></p>
<div class="hljs"><pre><code class="hljs xml"><span class="hljs-meta">&lt;?xml version="1.0" encoding="UTF-8"?&gt;</span>
<span class="hljs-tag">&lt;<span class="hljs-name">beans</span> <span class="hljs-attr">xmlns</span>=<span class="hljs-string">"http://www.springframework.org/schema/beans"</span>
	<span class="hljs-attr">xmlns:xsi</span>=<span class="hljs-string">"http://www.w3.org/2001/XMLSchema-instance"</span>
	<span class="hljs-attr">xsi:schemaLocation</span>=<span class="hljs-string">"http://www.springframework.org/schema/beans 
	http://www.springframework.org/schema/beans/spring-beans.xsd"</span>&gt;</span>

	<span class="hljs-comment">&lt;!-- 1: 配置数据源 --&gt;</span>
	<span class="hljs-tag">&lt;<span class="hljs-name">bean</span> <span class="hljs-attr">id</span>=<span class="hljs-string">"dataSource"</span> <span class="hljs-attr">class</span>=<span class="hljs-string">"org.springframework.jdbc.datasource.DriverManagerDataSource"</span>&gt;</span>
		<span class="hljs-comment">&lt;!-- 数据库驱动 --&gt;</span>
		<span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"driverClassName"</span> <span class="hljs-attr">value</span>=<span class="hljs-string">"com.mysql.cj.jdbc.Driver"</span> /&gt;</span>
		<span class="hljs-comment">&lt;!-- 连接数据库的url --&gt;</span>
		<span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"url"</span>
			<span class="hljs-attr">value</span>=<span class="hljs-string">"jdbc:mysql://localhost:3306/xxxxxx"</span>/&gt;</span>
		<span class="hljs-comment">&lt;!-- 连接数据库的用户名 --&gt;</span>
		<span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"username"</span> <span class="hljs-attr">value</span>=<span class="hljs-string">"xxxxxx"</span> /&gt;</span>
		<span class="hljs-comment">&lt;!-- 连接数据库的密码 --&gt;</span>
		<span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"password"</span> <span class="hljs-attr">value</span>=<span class="hljs-string">"xxxxxx"</span> /&gt;</span>
	<span class="hljs-tag">&lt;/<span class="hljs-name">bean</span>&gt;</span>

	<span class="hljs-comment">&lt;!-- 2: 配置JDBC模板 --&gt;</span>
	<span class="hljs-tag">&lt;<span class="hljs-name">bean</span> <span class="hljs-attr">id</span>=<span class="hljs-string">"jdbcTemplate"</span> <span class="hljs-attr">class</span>=<span class="hljs-string">"org.springframework.jdbc.core.JdbcTemplate"</span>&gt;</span>
		<span class="hljs-comment">&lt;!-- 默认必须使用数据源 --&gt;</span>
		<span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"dataSource"</span> <span class="hljs-attr">ref</span>=<span class="hljs-string">"dataSource"</span> /&gt;</span>
	<span class="hljs-tag">&lt;/<span class="hljs-name">bean</span>&gt;</span>

	<span class="hljs-comment">&lt;!-- 3: 配置注入类 --&gt;</span>
	<span class="hljs-tag">&lt;<span class="hljs-name">bean</span> <span class="hljs-attr">id</span>=<span class="hljs-string">"xxxxxx"</span> <span class="hljs-attr">class</span>=<span class="hljs-string">"xxxxxx"</span>&gt;</span>
		<span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"jdbcTemplate"</span> <span class="hljs-attr">ref</span>=<span class="hljs-string">"jdbcTemplate"</span> /&gt;</span>
	<span class="hljs-tag">&lt;/<span class="hljs-name">bean</span>&gt;</span>

    ······
<span class="hljs-tag">&lt;/<span class="hljs-name">beans</span>&gt;</span></code></pre></div>
<p><em>定义JdbcTemplate时,需要将dataSource注入到JdbcTemplate中,而其它需要使用JdbcTemplate的Bean,也需要将JdbcTemplate注入到该Bean中(<code>通常注入到Dao类中,在Dao类中进行与数据库的相关操作</code>).</em></p>
<h3 id="Spring-JdbcTempalte-的常用方法"><a href="#Spring-JdbcTempalte-的常用方法" class="headerlink" title="Spring JdbcTempalte 的常用方法"></a>Spring JdbcTempalte 的常用方法</h3><p><em><code>JdbcTemplate</code>类中提供了大量的操作数据库的方法,下面通过一个简单的<code>CURE</code>来体现一下Spring JDBC代码的简洁美 ~</em></p>
<ol>
<li><p><em>applicationContext.xml : 配置文件</em></p>
<div class="hljs"><pre><code class="hljs xml"><span class="hljs-meta">&lt;?xml version="1.0" encoding="UTF-8"?&gt;</span>
<span class="hljs-tag">&lt;<span class="hljs-name">beans</span> <span class="hljs-attr">xmlns</span>=<span class="hljs-string">"http://www.springframework.org/schema/beans"</span>
	<span class="hljs-attr">xmlns:xsi</span>=<span class="hljs-string">"http://www.w3.org/2001/XMLSchema-instance"</span>
	<span class="hljs-attr">xsi:schemaLocation</span>=<span class="hljs-string">"http://www.springframework.org/schema/beans 
	http://www.springframework.org/schema/beans/spring-beans.xsd"</span>&gt;</span>

	<span class="hljs-comment">&lt;!-- 1: 配置数据源 --&gt;</span>
	<span class="hljs-tag">&lt;<span class="hljs-name">bean</span> <span class="hljs-attr">id</span>=<span class="hljs-string">"dataSource"</span> <span class="hljs-attr">class</span>=<span class="hljs-string">"org.springframework.jdbc.datasource.DriverManagerDataSource"</span>&gt;</span>
		<span class="hljs-comment">&lt;!-- 数据库驱动 --&gt;</span>
		<span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"driverClassName"</span> <span class="hljs-attr">value</span>=<span class="hljs-string">"com.mysql.cj.jdbc.Driver"</span> /&gt;</span>
		<span class="hljs-comment">&lt;!-- 连接数据库的url --&gt;</span>
		<span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"url"</span> <span class="hljs-attr">value</span>=<span class="hljs-string">"jdbc:mysql://localhost/Spring?useSSL=false&amp;amp;serverTimezone=UTC&amp;amp;allowPublicKeyRetrieval=true"</span> /&gt;</span>
		<span class="hljs-comment">&lt;!-- 连接数据库的用户名 --&gt;</span>
		<span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"username"</span> <span class="hljs-attr">value</span>=<span class="hljs-string">"xxxxxx"</span> /&gt;</span>
		<span class="hljs-comment">&lt;!-- 连接数据库的密码 --&gt;</span>
		<span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"password"</span> <span class="hljs-attr">value</span>=<span class="hljs-string">"xxxxxx"</span> /&gt;</span>
	<span class="hljs-tag">&lt;/<span class="hljs-name">bean</span>&gt;</span>

	<span class="hljs-comment">&lt;!-- 2: 配置JDBC模板 --&gt;</span>
	<span class="hljs-tag">&lt;<span class="hljs-name">bean</span> <span class="hljs-attr">id</span>=<span class="hljs-string">"jdbcTemplate"</span>
		<span class="hljs-attr">class</span>=<span class="hljs-string">"org.springframework.jdbc.core.JdbcTemplate"</span>&gt;</span>
		<span class="hljs-comment">&lt;!-- 默认必须使用数据源 --&gt;</span>
		<span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"dataSource"</span> <span class="hljs-attr">ref</span>=<span class="hljs-string">"dataSource"</span> /&gt;</span>
	<span class="hljs-tag">&lt;/<span class="hljs-name">bean</span>&gt;</span>

	<span class="hljs-comment">&lt;!-- 3: 配置注入类 --&gt;</span>
	<span class="hljs-tag">&lt;<span class="hljs-name">bean</span> <span class="hljs-attr">id</span>=<span class="hljs-string">"accountDao"</span> <span class="hljs-attr">class</span>=<span class="hljs-string">"pers.huangyuhui.spring.jdbc.dao.impl.AccountDaoImpl"</span>&gt;</span>
		<span class="hljs-comment">&lt;!-- 将jbdcTemplate注入到accountDao实例中 --&gt;</span>
		<span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"jdbcTemplate"</span> <span class="hljs-attr">ref</span>=<span class="hljs-string">"jdbcTemplate"</span> /&gt;</span>
	<span class="hljs-tag">&lt;/<span class="hljs-name">bean</span>&gt;</span>

<span class="hljs-tag">&lt;/<span class="hljs-name">beans</span>&gt;</span></code></pre></div>
</li>
<li><p><em>Account.java : 封装用户账户信息</em></p>
<div class="hljs"><pre><code class="hljs java"><span class="hljs-keyword">package</span> pers.huangyuhui.spring.jdbc.bean;

<span class="hljs-comment">/**
 * <span class="hljs-doctag">@ClassName</span>: Account
 * <span class="hljs-doctag">@Description</span>: 用户账户信息实体表
 * <span class="hljs-doctag">@author</span>: HuangYuhui
 * <span class="hljs-doctag">@date</span>: May 17, 2019 11:56:41 AM
 *
 */</span>
<span class="hljs-keyword">public</span> <span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">Account</span> </span>&#123;

	<span class="hljs-keyword">private</span> Integer id;
	<span class="hljs-keyword">private</span> String username;
	<span class="hljs-keyword">private</span> Double balance;

	<span class="hljs-function"><span class="hljs-keyword">public</span> Integer <span class="hljs-title">getId</span><span class="hljs-params">()</span> </span>&#123;
		<span class="hljs-keyword">return</span> id;
	&#125;

	<span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setId</span><span class="hljs-params">(Integer id)</span> </span>&#123;
		<span class="hljs-keyword">this</span>.id = id;
	&#125;

	<span class="hljs-function"><span class="hljs-keyword">public</span> String <span class="hljs-title">getUsername</span><span class="hljs-params">()</span> </span>&#123;
		<span class="hljs-keyword">return</span> username;
	&#125;

	<span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setUsername</span><span class="hljs-params">(String username)</span> </span>&#123;
		<span class="hljs-keyword">this</span>.username = username;
	&#125;

	<span class="hljs-function"><span class="hljs-keyword">public</span> Double <span class="hljs-title">getBalance</span><span class="hljs-params">()</span> </span>&#123;
		<span class="hljs-keyword">return</span> balance;
	&#125;

	<span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setBalance</span><span class="hljs-params">(Double balance)</span> </span>&#123;
		<span class="hljs-keyword">this</span>.balance = balance;
	&#125;

	<span class="hljs-meta">@Override</span>
	<span class="hljs-function"><span class="hljs-keyword">public</span> String <span class="hljs-title">toString</span><span class="hljs-params">()</span> </span>&#123;
		<span class="hljs-keyword">return</span> <span class="hljs-string">"Account [id="</span> + id + <span class="hljs-string">", username="</span> + username + <span class="hljs-string">", balance="</span> + balance + <span class="hljs-string">"]"</span>;
	&#125;

&#125;</code></pre></div>
</li>
<li><p><em>AccountDao.java : 操作账户信息的接口</em></p>
<div class="hljs"><pre><code class="hljs java"><span class="hljs-keyword">package</span> pers.huangyuhui.spring.jdbc.dao;

<span class="hljs-keyword">import</span> java.util.List;

<span class="hljs-keyword">import</span> pers.huangyuhui.spring.jdbc.bean.Account;

<span class="hljs-comment">/**
 * <span class="hljs-doctag">@ClassName</span>: AccountDao
 * <span class="hljs-doctag">@Description</span>: 操作用户账户表信息
 * <span class="hljs-doctag">@author</span>: HuangYuhui
 * <span class="hljs-doctag">@date</span>: May 17, 2019 12:00:43 PM
 *
 */</span>
<span class="hljs-keyword">public</span> <span class="hljs-class"><span class="hljs-keyword">interface</span> <span class="hljs-title">AccountDao</span> </span>&#123;

	<span class="hljs-comment">/**
	 * <span class="hljs-doctag">@Title</span>: createTable
	 * <span class="hljs-doctag">@Description</span>: 创建数据表
	 * <span class="hljs-doctag">@param</span>: sql
	 * <span class="hljs-doctag">@return</span>: void
	 */</span>
	<span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">createTable</span><span class="hljs-params">(String sql)</span></span>;

	<span class="hljs-comment">/**
	 * <span class="hljs-doctag">@Title</span>: findAccountById
	 * <span class="hljs-doctag">@Description</span>: 查找表数据
	 * <span class="hljs-doctag">@param</span>: id
	 * <span class="hljs-doctag">@return</span>: Account
	 */</span>
	<span class="hljs-function"><span class="hljs-keyword">public</span> Account <span class="hljs-title">findAccountById</span><span class="hljs-params">(<span class="hljs-keyword">int</span> id)</span></span>;

	<span class="hljs-comment">/**
	 * <span class="hljs-doctag">@Title</span>: findAllAccount
	 * <span class="hljs-doctag">@Description</span>: 查找全部表数据
	 * <span class="hljs-doctag">@return</span>: List&lt;Account&gt;
	 */</span>
	<span class="hljs-function"><span class="hljs-keyword">public</span> List&lt;Account&gt; <span class="hljs-title">findAllAccount</span><span class="hljs-params">()</span></span>;

	<span class="hljs-comment">/**
	 * <span class="hljs-doctag">@Title</span>: addAccount
	 * <span class="hljs-doctag">@Description</span>: 添加表数据
	 * <span class="hljs-doctag">@param</span>: account
	 * <span class="hljs-doctag">@return</span>: int
	 */</span>
	<span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">int</span> <span class="hljs-title">addAccount</span><span class="hljs-params">(Account account)</span></span>;

	<span class="hljs-comment">/**
	 * <span class="hljs-doctag">@Title</span>: updateAccount
	 * <span class="hljs-doctag">@Description</span>: 更新表数据
	 * <span class="hljs-doctag">@param</span>: account
	 * <span class="hljs-doctag">@return</span>: int
	 */</span>
	<span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">int</span> <span class="hljs-title">updateAccount</span><span class="hljs-params">(Account account)</span></span>;

	<span class="hljs-comment">/**
	 * <span class="hljs-doctag">@Title</span>: deleteAccount
	 * <span class="hljs-doctag">@Description</span>: 删除表数据
	 * <span class="hljs-doctag">@param</span>: id
	 * <span class="hljs-doctag">@return</span>: int
	 */</span>
	<span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">int</span> <span class="hljs-title">deleteAccount</span><span class="hljs-params">(<span class="hljs-keyword">int</span> id)</span></span>;
&#125;</code></pre></div>
</li>
<li><p><em>AccountDaoImpl.java : AccountDao的实现类</em></p>
<div class="hljs"><pre><code class="hljs java"><span class="hljs-keyword">package</span> pers.huangyuhui.spring.jdbc.dao.impl;

<span class="hljs-keyword">import</span> java.util.List;

<span class="hljs-keyword">import</span> org.springframework.jdbc.core.BeanPropertyRowMapper;
<span class="hljs-keyword">import</span> org.springframework.jdbc.core.JdbcTemplate;
<span class="hljs-keyword">import</span> org.springframework.jdbc.core.RowMapper;

<span class="hljs-keyword">import</span> pers.huangyuhui.spring.jdbc.bean.Account;
<span class="hljs-keyword">import</span> pers.huangyuhui.spring.jdbc.dao.AccountDao;

<span class="hljs-comment">/**
 * <span class="hljs-doctag">@ClassName</span>: AccountDaoImpl
 * <span class="hljs-doctag">@Description</span>: AccountDao接口的实现类
 * <span class="hljs-doctag">@author</span>: HuangYuhui
 * <span class="hljs-doctag">@date</span>: May 17, 2019 12:05:34 PM
 *
 */</span>
<span class="hljs-keyword">public</span> <span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">AccountDaoImpl</span> <span class="hljs-keyword">implements</span> <span class="hljs-title">AccountDao</span> </span>&#123;

	<span class="hljs-comment">// 声明JdbcTemplate属性及其setter方法</span>
	<span class="hljs-keyword">private</span> JdbcTemplate jdbcTemplate;

	<span class="hljs-comment">// 获取JdbcTemplate实例</span>
	<span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setJdbcTemplate</span><span class="hljs-params">(JdbcTemplate jdbcTemplate)</span> </span>&#123;
		<span class="hljs-keyword">this</span>.jdbcTemplate = jdbcTemplate;
	&#125;

	<span class="hljs-meta">@Override</span>
	<span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">createTable</span><span class="hljs-params">(String sql)</span> </span>&#123;

		jdbcTemplate.execute(sql);
	&#125;

	<span class="hljs-meta">@Override</span>
	<span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">int</span> <span class="hljs-title">addAccount</span><span class="hljs-params">(Account account)</span> </span>&#123;
		String sql = <span class="hljs-string">"insert into account(username,balance) value(?,?)"</span>;

		<span class="hljs-comment">// 定义数组来存储SQL语句中的参数. Good idea ~</span>
		Object[] objects = <span class="hljs-keyword">new</span> Object[] &#123; account.getUsername(), account.getBalance() &#125;;

		<span class="hljs-comment">// 执行添加操作,返回受SQL语句影响的条数</span>
		<span class="hljs-keyword">return</span> jdbcTemplate.update(sql, objects);
	&#125;

	<span class="hljs-meta">@Override</span>
	<span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">int</span> <span class="hljs-title">updateAccount</span><span class="hljs-params">(Account account)</span> </span>&#123;
		String sql = <span class="hljs-string">"update account set username=? , balance=?  where id = ?"</span>;

		<span class="hljs-comment">// 注意: `?`需与设置的参数顺序对应哟 !</span>
		Object[] objects = <span class="hljs-keyword">new</span> Object[] &#123; account.getUsername(), account.getBalance(), account.getId() &#125;;

		<span class="hljs-keyword">return</span> <span class="hljs-keyword">this</span>.jdbcTemplate.update(sql, objects);
	&#125;

	<span class="hljs-meta">@Override</span>
	<span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">int</span> <span class="hljs-title">deleteAccount</span><span class="hljs-params">(<span class="hljs-keyword">int</span> id)</span> </span>&#123;
		String sql = <span class="hljs-string">"delete from account where id = ?"</span>;

		<span class="hljs-keyword">return</span> <span class="hljs-keyword">this</span>.jdbcTemplate.update(sql, id);
	&#125;

	<span class="hljs-meta">@Override</span>
	<span class="hljs-function"><span class="hljs-keyword">public</span> Account <span class="hljs-title">findAccountById</span><span class="hljs-params">(<span class="hljs-keyword">int</span> id)</span> </span>&#123;
		String sql = <span class="hljs-string">"select id,username,balance from account where id = ?"</span>;

		<span class="hljs-comment">// 创建BeanPropertyRowMapper对象</span>
		<span class="hljs-comment">// 它可以自动地将数据表中的数据映射到用户自定义的类中(前提是:用户自定义类中的字段要与数据表中的字段相对应)</span>
		RowMapper&lt;Account&gt; rowMapper = <span class="hljs-keyword">new</span> BeanPropertyRowMapper&lt;Account&gt;(Account<span class="hljs-class">.<span class="hljs-keyword">class</span>)</span>;

		<span class="hljs-comment">// 将id绑定到SQL语句中,并通过RowMapper返回一个Object类型的单行记录</span>
		<span class="hljs-keyword">return</span> <span class="hljs-keyword">this</span>.jdbcTemplate.queryForObject(sql, rowMapper, id);
	&#125;

	<span class="hljs-meta">@Override</span>
	<span class="hljs-function"><span class="hljs-keyword">public</span> List&lt;Account&gt; <span class="hljs-title">findAllAccount</span><span class="hljs-params">()</span> </span>&#123;
		String sql = <span class="hljs-string">"select id,username,balance from account"</span>;

		RowMapper&lt;Account&gt; rowMapper = <span class="hljs-keyword">new</span> BeanPropertyRowMapper&lt;Account&gt;(Account<span class="hljs-class">.<span class="hljs-keyword">class</span>)</span>;

		<span class="hljs-comment">// 执行静态的SQL查询,并通过RowMapper返回结果集</span>
		<span class="hljs-keyword">return</span> <span class="hljs-keyword">this</span>.jdbcTemplate.query(sql, rowMapper);

	&#125;

&#125;</code></pre></div>

</li>
</ol>
<p>5.<em>CURETest.java : 测试类</em></p>
<div class="hljs"><pre><code class="hljs java"><span class="hljs-keyword">package</span> pers.huangyuhui.spring.jdbc.test;

<span class="hljs-keyword">import</span> java.util.List;

<span class="hljs-keyword">import</span> org.junit.BeforeClass;
<span class="hljs-keyword">import</span> org.junit.Ignore;
<span class="hljs-keyword">import</span> org.junit.Test;
<span class="hljs-keyword">import</span> org.springframework.context.ApplicationContext;
<span class="hljs-keyword">import</span> org.springframework.context.support.ClassPathXmlApplicationContext;

<span class="hljs-keyword">import</span> pers.huangyuhui.spring.jdbc.bean.Account;
<span class="hljs-keyword">import</span> pers.huangyuhui.spring.jdbc.dao.AccountDao;

<span class="hljs-comment">/**
 * <span class="hljs-doctag">@ClassName</span>: CURDTest
 * <span class="hljs-doctag">@Description</span>: 测试Spring JDBC的增删改查功能
 * <span class="hljs-doctag">@author</span>: HuangYuhui
 * <span class="hljs-doctag">@date</span>: May 17, 2019 12:26:43 PM
 *
 */</span>
<span class="hljs-keyword">public</span> <span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">CURDTest</span> </span>&#123;

	<span class="hljs-keyword">private</span> <span class="hljs-keyword">static</span> Account account;
	<span class="hljs-keyword">private</span> <span class="hljs-keyword">static</span> AccountDao accountDao;
	<span class="hljs-keyword">private</span> <span class="hljs-keyword">static</span> ApplicationContext applicationContext;

	<span class="hljs-meta">@BeforeClass</span>
	<span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> <span class="hljs-title">init</span><span class="hljs-params">()</span> </span>&#123;
		account = <span class="hljs-keyword">new</span> Account();
		<span class="hljs-comment">// 加载配置文件</span>
		applicationContext = <span class="hljs-keyword">new</span> ClassPathXmlApplicationContext(<span class="hljs-string">"applicationContext.xml"</span>);
		<span class="hljs-comment">// 获取AccountDao实例</span>
		accountDao = (AccountDao) applicationContext.getBean(<span class="hljs-string">"accountDao"</span>);
	&#125;

	<span class="hljs-meta">@Test</span>
	<span class="hljs-comment">// 创建用户账户信息数据表</span>
	<span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">createTableTest</span><span class="hljs-params">()</span> </span>&#123;
		String sql = 
				<span class="hljs-string">"create table account\r\n"</span> + 
				<span class="hljs-string">"(\r\n"</span> + 
				<span class="hljs-string">"	id int primary key auto_increment,\r\n"</span> + 
				<span class="hljs-string">"	username varchar(10) not null,\r\n"</span> + 
				<span class="hljs-string">"	balance double not null\r\n"</span> + 
				<span class="hljs-string">")"</span>;

		accountDao.createTable(sql);
		System.out.println(<span class="hljs-string">"success to create the table of account ~"</span>);
	&#125;

	<span class="hljs-meta">@Ignore</span>
	<span class="hljs-meta">@Test</span>
	<span class="hljs-comment">// 添加账户信息</span>
	<span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">addAccountTest</span><span class="hljs-params">()</span> </span>&#123;

		<span class="hljs-comment">// 向Account对象中添加数据</span>
		account.setUsername(<span class="hljs-string">"YUbuntu0109"</span>);
		account.setBalance(<span class="hljs-number">666666.0</span>);

		<span class="hljs-comment">// 获取添加操作返回的结果</span>
		<span class="hljs-keyword">int</span> num = accountDao.addAccount(account);
		<span class="hljs-keyword">if</span> (num &gt; <span class="hljs-number">0</span>) &#123;
			System.out.println(<span class="hljs-string">"成功添加了 "</span> + num + <span class="hljs-string">" 条数据哟 ~"</span>);
		&#125; <span class="hljs-keyword">else</span> &#123;
			System.out.println(<span class="hljs-string">"添加数据失败 !"</span>);
		&#125;

	&#125;

	<span class="hljs-meta">@Ignore</span>
	<span class="hljs-meta">@Test</span>
	<span class="hljs-comment">// 更新指定账户信息</span>
	<span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">updateAccountTest</span><span class="hljs-params">()</span> </span>&#123;

		account.setId(<span class="hljs-number">3</span>);
		account.setUsername(<span class="hljs-string">"update"</span>);
		account.setBalance(<span class="hljs-number">999999.0</span>);

		<span class="hljs-keyword">int</span> num = accountDao.updateAccount(account);
		<span class="hljs-keyword">if</span> (num &gt; <span class="hljs-number">0</span>) &#123;
			System.out.println(<span class="hljs-string">"更新成功了 "</span> + num + <span class="hljs-string">" 条数据哟 ~"</span>);
		&#125; <span class="hljs-keyword">else</span> &#123;
			System.out.println(<span class="hljs-string">"更新数据失败 ! "</span>);
		&#125;

	&#125;

	<span class="hljs-meta">@Ignore</span>
	<span class="hljs-meta">@Test</span>
	<span class="hljs-comment">// 删除指定账户信息</span>
	<span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">deleteAccountTest</span><span class="hljs-params">()</span> </span>&#123;

		<span class="hljs-keyword">int</span> num = accountDao.deleteAccount(<span class="hljs-number">1</span>);
		<span class="hljs-keyword">if</span> (num &gt; <span class="hljs-number">0</span>) &#123;
			System.out.println(<span class="hljs-string">"删除了 "</span> + num + <span class="hljs-string">" 条数据哟 ~"</span>);
		&#125; <span class="hljs-keyword">else</span> &#123;
			System.out.println(<span class="hljs-string">"删除数据失败 !"</span>);
		&#125;
	&#125;

	<span class="hljs-meta">@Ignore</span>
	<span class="hljs-meta">@Test</span>
	<span class="hljs-comment">// 查询指定账户信息</span>
	<span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">findAccountByIdTest</span><span class="hljs-params">()</span> </span>&#123;
		account = accountDao.findAccountById(<span class="hljs-number">2</span>);

		System.out.println(account);
	&#125;

	<span class="hljs-meta">@Ignore</span>
	<span class="hljs-meta">@Test</span>
	<span class="hljs-comment">// 查询所有账户信息</span>
	<span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">findAllAccount</span><span class="hljs-params">()</span> </span>&#123;
		List&lt;Account&gt; accountInfo = accountDao.findAllAccount();

		System.out.println(accountInfo);

	&#125;

&#125;</code></pre></div>



            </article>
            <hr>
            <div>
              <div class="post-metas mb-3">
                
                
                  <div class="post-meta">
                    <i class="iconfont icon-tags"></i>
                    
                      <a class="hover-with-bg" href="/tags/Spring/">Spring</a>
                    
                  </div>
                
              </div>
              
                <p class="note note-warning">本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-sa/4.0/deed.zh" target="_blank" rel="nofollow noopener noopener">CC BY-SA 4.0 协议</a> ，转载请注明出处！</p>
              
              
                <div class="post-prevnext row">
                  <div class="post-prev col-6">
                    
                    
                      <a href="/2019/05/16/MyBatis-%E5%8A%A8%E6%80%81SQL/">
                        <i class="iconfont icon-arrowleft"></i>
                        <span class="hidden-mobile">MyBatis:动态SQL</span>
                        <span class="visible-mobile">Previous</span>
                      </a>
                    
                  </div>
                  <div class="post-next col-6">
                    
                    
                      <a href="/2019/05/15/VOA-One-Hour-of-Exercise-a-Day-Fights-Damage-of-Sitting/">
                        <span class="hidden-mobile">VOA:One Hour of Exercise a Day Fights Damage of Sitting</span>
                        <span class="visible-mobile">Next</span>
                        <i class="iconfont icon-arrowright"></i>
                      </a>
                    
                  </div>
                </div>
              
            </div>

            
          </div>
        </div>
      </div>
    </div>
    
      <div class="d-none d-lg-block col-lg-2 toc-container" id="toc-ctn">
        <div id="toc">
  <p class="toc-header"><i class="iconfont icon-list"></i>&nbsp;TOC</p>
  <div id="tocbot"></div>
</div>

      </div>
    
  </div>
</div>

<!-- Custom -->


    
  </main>

  
    <a id="scroll-top-button" href="#" role="button">
      <i class="iconfont icon-arrowup" aria-hidden="true"></i>
    </a>
  

  
    <div class="modal fade" id="modalSearch" tabindex="-1" role="dialog" aria-labelledby="ModalLabel"
     aria-hidden="true">
  <div class="modal-dialog modal-dialog-scrollable modal-lg" role="document">
    <div class="modal-content">
      <div class="modal-header text-center">
        <h4 class="modal-title w-100 font-weight-bold">Search</h4>
        <button type="button" id="local-search-close" class="close" data-dismiss="modal" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <div class="modal-body mx-3">
        <div class="md-form mb-5">
          <input type="text" id="local-search-input" class="form-control validate">
          <label data-error="x" data-success="v"
                 for="local-search-input">keyword</label>
        </div>
        <div class="list-group" id="local-search-result"></div>
      </div>
    </div>
  </div>
</div>
  

  

  

  <footer class="mt-5">
  <div class="text-center py-3">
    <div>
      <a href="https://hexo.io" target="_blank" rel="nofollow noopener"><span>Hexo</span></a>
      <i class="iconfont icon-love"></i>
      <a href="https://github.com/fluid-dev/hexo-theme-fluid" target="_blank" rel="nofollow noopener">
        <span>Fluid</span></a>
    </div>
    
  <div class="statistics">
    
    

    
      
        <!-- 不蒜子统计PV -->
        <span id="busuanzi_container_site_pv" style="display: none">
            总访问量 
            <span id="busuanzi_value_site_pv"></span>
             次
          </span>
      
      
        <!-- 不蒜子统计UV -->
        <span id="busuanzi_container_site_uv" style="display: none">
            总访客数 
            <span id="busuanzi_value_site_uv"></span>
             人
          </span>
      
    
  </div>


    

    
  </div>
</footer>

<!-- SCRIPTS -->
<script  src="https://cdn.staticfile.org/jquery/3.4.1/jquery.min.js" ></script>
<script  src="https://cdn.staticfile.org/twitter-bootstrap/4.4.1/js/bootstrap.min.js" ></script>
<script  src="/js/debouncer.js" ></script>
<script  src="/js/main.js" ></script>

<!-- Plugins -->


  
    <script  src="/js/lazyload.js" ></script>
  



  <script defer src="https://cdn.staticfile.org/clipboard.js/2.0.6/clipboard.min.js" ></script>
  <script  src="/js/clipboard-use.js" ></script>



  <script defer src="https://busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js" ></script>





  <script  src="https://cdn.staticfile.org/tocbot/4.11.1/tocbot.min.js" ></script>
  <script>
    $(document).ready(function () {
      var boardCtn = $('#board-ctn');
      var boardTop = boardCtn.offset().top;

      tocbot.init({
        tocSelector: '#tocbot',
        contentSelector: 'article.markdown-body',
        headingSelector: 'h1,h2,h3,h4,h5,h6',
        linkClass: 'tocbot-link',
        activeLinkClass: 'tocbot-active-link',
        listClass: 'tocbot-list',
        isCollapsedClass: 'tocbot-is-collapsed',
        collapsibleClass: 'tocbot-is-collapsible',
        collapseDepth: 0,
        scrollSmooth: true,
        headingsOffset: -boardTop
      });
      if ($('.toc-list-item').length > 0) {
        $('#toc').css('visibility', 'visible');
      }
    });
  </script>



  <script  src="https://cdn.staticfile.org/typed.js/2.0.11/typed.min.js" ></script>
  <script>
    var typed = new Typed('#subtitle', {
      strings: [
        '  ',
        "Spring:数据库开发&nbsp;",
      ],
      cursorChar: "_",
      typeSpeed: 70,
      loop: false,
    });
    typed.stop();
    $(document).ready(function () {
      $(".typed-cursor").addClass("h2");
      typed.start();
    });
  </script>



  <script  src="https://cdn.staticfile.org/anchor-js/4.2.2/anchor.min.js" ></script>
  <script>
    anchors.options = {
      placement: "right",
      visible: "hover",
      
    };
    var el = "h1,h2,h3,h4,h5,h6".split(",");
    var res = [];
    for (item of el) {
      res.push(".markdown-body > " + item)
    }
    anchors.add(res.join(", "))
  </script>



  <script  src="/js/local-search.js" ></script>
  <script>
    var path = "/local-search.xml";
    var inputArea = document.querySelector("#local-search-input");
    inputArea.onclick = function () {
      searchFunc(path, 'local-search-input', 'local-search-result');
      this.onclick = null
    }
  </script>



  <script  src="https://cdn.staticfile.org/fancybox/3.5.7/jquery.fancybox.min.js" ></script>
  <link  rel="stylesheet" href="https://cdn.staticfile.org/fancybox/3.5.7/jquery.fancybox.min.css" />

  <script>
    $('#post img:not(.no-zoom img, img[no-zoom]), img[zoom]').each(
      function () {
        var element = document.createElement('a');
        $(element).attr('data-fancybox', 'images');
        $(element).attr('href', $(this).attr('src'));
        $(this).wrap(element);
      }
    );
  </script>







  
  
    <script>
      !function (e, t, a) {
        function r() {
          for (var e = 0; e < s.length; e++) s[e].alpha <= 0 ? (t.body.removeChild(s[e].el), s.splice(e, 1)) : (s[e].y--, s[e].scale += .004, s[e].alpha -= .013, s[e].el.style.cssText = "left:" + s[e].x + "px;top:" + s[e].y + "px;opacity:" + s[e].alpha + ";transform:scale(" + s[e].scale + "," + s[e].scale + ") rotate(45deg);background:" + s[e].color + ";z-index:99999");
          requestAnimationFrame(r)
        }

        function n() {
          var t = "function" == typeof e.onclick && e.onclick;
          e.onclick = function (e) {
            t && t(), o(e)
          }
        }

        function o(e) {
          var a = t.createElement("div");
          a.className = "heart", s.push({
            el: a,
            x: e.clientX - 5,
            y: e.clientY - 5,
            scale: 1,
            alpha: 1,
            color: c()
          }), t.body.appendChild(a)
        }

        function i(e) {
          var a = t.createElement("style");
          a.type = "text/css";
          try {
            a.appendChild(t.createTextNode(e))
          } catch (t) {
            a.styleSheet.cssText = e
          }
          t.getElementsByTagName("head")[0].appendChild(a)
        }

        function c() {
          return "rgb(" + ~~(255 * Math.random()) + "," + ~~(255 * Math.random()) + "," + ~~(255 * Math.random()) + ")"
        }

        var s = [];
        e.requestAnimationFrame = e.requestAnimationFrame || e.webkitRequestAnimationFrame || e.mozRequestAnimationFrame || e.oRequestAnimationFrame || e.msRequestAnimationFrame || function (e) {
          setTimeout(e, 1e3 / 60)
        }, i(".heart{width: 10px;height: 10px;position: fixed;background: #f00;transform: rotate(45deg);-webkit-transform: rotate(45deg);-moz-transform: rotate(45deg);}.heart:after,.heart:before{content: '';width: inherit;height: inherit;background: inherit;border-radius: 50%;-webkit-border-radius: 50%;-moz-border-radius: 50%;position: fixed;}.heart:after{top: -5px;}.heart:before{left: -5px;}"), n(), r()
      }(window, document);
    </script>
  














</body>
</html>
